﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using DbHelper;//自定义帮助类
using Gx.Model.Entity;//实体
using Gx.Common;//通用类
namespace Gx.Dal.Employee
{
    public partial class G_PurchaseOrderPriceInfoDal : SqlServerHelper, IDisposable
    {
        public bool AddG_PurchaseOrderPriceInfo(G_PurchaseOrderPriceInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO G_PurchaseOrderPriceInfo");
            strSql.Append("(");
            strSql.Append("PurchaseOrderId,");
            strSql.Append("Price,");
            strSql.Append("Remart,");
            strSql.Append("Status,");
            strSql.Append("AddUser,");
            strSql.Append("AddTime,");
            strSql.Append("LastEditUser,");
            strSql.Append("LastEditTime");
            strSql.Append(")");
            strSql.Append("VALUES");
            strSql.Append("(");
            strSql.Append("@PurchaseOrderId,");
            strSql.Append("@Price,");
            strSql.Append("@Remart,");
            strSql.Append("@Status,");
            strSql.Append("@AddUser,");
            strSql.Append("@AddTime,");
            strSql.Append("@LastEditUser,");
            strSql.Append("@LastEditTime");
            strSql.Append(")");
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@PurchaseOrderId", SqlDbType.Int, 4) { Value = model.PurchaseOrderId });
            pList.Add(new SqlParameter("@Price", SqlDbType.Decimal, 9) { Value = model.Price });
            pList.Add(new SqlParameter("@Remart", SqlDbType.NVarChar, 200) { Value = model.Remart });
            pList.Add(new SqlParameter("@Status", SqlDbType.Int, 4) { Value = model.Status });
            pList.Add(new SqlParameter("@AddUser", SqlDbType.Int, 4) { Value = model.AddUser });
            pList.Add(new SqlParameter("@AddTime", SqlDbType.DateTime) { Value = model.AddTime });
            pList.Add(new SqlParameter("@LastEditUser", SqlDbType.Int, 4) { Value = model.LastEditUser });
            pList.Add(new SqlParameter("@LastEditTime", SqlDbType.DateTime) { Value = model.LastEditTime });
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public bool DelG_PurchaseOrderPriceInfo(int PurchaseOrderPriceId)
        {
            string strSql = "DELETE FROM G_PurchaseOrderPriceInfo WHERE PurchaseOrderPriceId=@PurchaseOrderPriceId";
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@PurchaseOrderPriceId", SqlDbType.Int, 4) { Value = PurchaseOrderPriceId });
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public bool DelG_PurchaseOrderPriceInfo(G_PurchaseOrderPriceInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE G_PurchaseOrderPriceInfo SET ");
            strSql.Append("[Status]=@Status  ");
            strSql.Append("LastEditUser=@LastEditUser, ");
            strSql.Append("LastEditTime=@LastEditTime ");
            strSql.Append("WHERE PurchaseOrderPriceId=@PurchaseOrderPriceId  ");
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@PurchaseOrderPriceId", SqlDbType.Int, 4) { Value = model.PurchaseOrderPriceId });
            pList.Add(new SqlParameter("@Status", SqlDbType.TinyInt) { Value = model.Status });
            pList.Add(new SqlParameter("@LastEditUser", SqlDbType.Int, 4) { Value = model.LastEditUser });
            pList.Add(new SqlParameter("@LastEditTime", SqlDbType.DateTime) { Value = model.LastEditTime });
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public bool EditG_PurchaseOrderPriceInfo(G_PurchaseOrderPriceInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE G_PurchaseOrderPriceInfo SET ");
            strSql.Append(" PurchaseOrderId=@PurchaseOrderId,");
            strSql.Append(" Price=@Price,");
            strSql.Append(" Remart=@Remart,");
            strSql.Append(" Status=@Status,");
            strSql.Append(" AddUser=@AddUser,");
            strSql.Append(" AddTime=@AddTime,");
            strSql.Append(" LastEditUser=@LastEditUser,");
            strSql.Append(" LastEditTime=@LastEditTime");
            strSql.Append(" WHERE PurchaseOrderPriceId=@PurchaseOrderPriceId");
            List<SqlParameter> pList = new List<SqlParameter>
            {
                new SqlParameter("@PurchaseOrderPriceId", SqlDbType.Int, 4) { Value = model.PurchaseOrderPriceId },
                new SqlParameter("@PurchaseOrderId", SqlDbType.Int, 4) { Value = model.PurchaseOrderId },
                new SqlParameter("@Price", SqlDbType.Decimal, 9) { Value = model.Price },
                new SqlParameter("@Remart", SqlDbType.NVarChar, 200) { Value = model.Remart },
                new SqlParameter("@Status", SqlDbType.Int, 4) { Value = model.Status },
                new SqlParameter("@AddUser", SqlDbType.Int, 4) { Value = model.AddUser },
                new SqlParameter("@AddTime", SqlDbType.DateTime) { Value = model.AddTime },
                new SqlParameter("@LastEditUser", SqlDbType.Int, 4) { Value = model.LastEditUser },
                new SqlParameter("@LastEditTime", SqlDbType.DateTime) { Value = model.LastEditTime }
            };
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public List<G_PurchaseOrderPriceInfo> GetG_PurchaseOrderPriceInfoList(string strWhere)
        {
            List<G_PurchaseOrderPriceInfo> list = new List<G_PurchaseOrderPriceInfo>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append(" PurchaseOrderPriceId");
            strSql.Append(",PurchaseOrderId");
            strSql.Append(",Price");
            strSql.Append(",Remart");
            strSql.Append(",Status");
            strSql.Append(",AddUser");
            strSql.Append(",AddTime");
            strSql.Append(",LastEditUser");
            strSql.Append(",LastEditTime");
            strSql.AppendFormat(" FROM G_PurchaseOrderPriceInfo WITH(NOLOCK) WHERE [Status]=0 {0}", strWhere);
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString()))
            {
                int PurchaseOrderPriceId = r.GetOrdinal("PurchaseOrderPriceId");
                int PurchaseOrderId = r.GetOrdinal("PurchaseOrderId");
                int Price = r.GetOrdinal("Price");
                int Remart = r.GetOrdinal("Remart");
                int Status = r.GetOrdinal("Status");
                int AddUser = r.GetOrdinal("AddUser");
                int AddTime = r.GetOrdinal("AddTime");
                int LastEditUser = r.GetOrdinal("LastEditUser");
                int LastEditTime = r.GetOrdinal("LastEditTime");
                G_PurchaseOrderPriceInfo model = null;
                while (r.Read())
                {
                    model = new G_PurchaseOrderPriceInfo();
                    model.PurchaseOrderPriceId = r.GetInt32(PurchaseOrderPriceId);
                    model.PurchaseOrderId = r.GetInt32(PurchaseOrderId);
                    model.Price = r.GetDecimal(Price);
                    model.Remart = r.GetString(Remart);
                    model.Status = r.GetInt32(Status);
                    model.AddUser = r.GetInt32(AddUser);
                    model.AddTime = r.GetDateTime(AddTime);
                    model.LastEditUser = r.GetInt32(LastEditUser);
                    model.LastEditTime = r.GetDateTime(LastEditTime);
                    list.Add(model);
                }
                r.Close();
            }
            return list;
        }

        public List<G_PurchaseOrderPriceInfo> GetG_PurchaseOrderPriceInfoList(string strWhere, int pageIndex, int pageSize, out int totalCount)
        {
            List<G_PurchaseOrderPriceInfo> list = new List<G_PurchaseOrderPriceInfo>();
            totalCount = GetG_PurchaseOrderPriceInfoCount(strWhere);
            if (totalCount == 0)
            {
                return list;
            }
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM (");
            strSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY PurchaseOrderPriceId DESC) AS ORDERBYID");
            strSql.Append(",PurchaseOrderPriceId");
            strSql.Append(",PurchaseOrderId");
            strSql.Append(",Price");
            strSql.Append(",Remart");
            strSql.Append(",Status");
            strSql.Append(",AddUser");
            strSql.Append(",AddTime");
            strSql.Append(",LastEditUser");
            strSql.Append(",LastEditTime");
            strSql.AppendFormat(" FROM G_PurchaseOrderPriceInfo WITH(NOLOCK) WHERE [Status]=0 {0}) AS T WHERE T.ORDERBYID  BETWEEN {1} AND {2}", strWhere, pageIndex, pageSize);
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString()))
            {
                int PurchaseOrderPriceId = r.GetOrdinal("PurchaseOrderPriceId");
                int PurchaseOrderId = r.GetOrdinal("PurchaseOrderId");
                int Price = r.GetOrdinal("Price");
                int Remart = r.GetOrdinal("Remart");
                int Status = r.GetOrdinal("Status");
                int AddUser = r.GetOrdinal("AddUser");
                int AddTime = r.GetOrdinal("AddTime");
                int LastEditUser = r.GetOrdinal("LastEditUser");
                int LastEditTime = r.GetOrdinal("LastEditTime");
                G_PurchaseOrderPriceInfo model = null;
                while (r.Read())
                {
                    model = new G_PurchaseOrderPriceInfo();
                    model.PurchaseOrderPriceId = r.GetInt32(PurchaseOrderPriceId);
                    model.PurchaseOrderId = r.GetInt32(PurchaseOrderId);
                    model.Price = r.GetDecimal(Price);
                    model.Remart = r.GetString(Remart);
                    model.Status = r.GetInt32(Status);
                    model.AddUser = r.GetInt32(AddUser);
                    model.AddTime = r.GetDateTime(AddTime);
                    model.LastEditUser = r.GetInt32(LastEditUser);
                    model.LastEditTime = r.GetDateTime(LastEditTime);
                    list.Add(model);
                }
                r.Close();
            }
            return list;
        }

        public int GetG_PurchaseOrderPriceInfoCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("SELECT COUNT(*) FROM G_PurchaseOrderPriceInfo WITH(NOLOCK) WHERE [Status]=0 {0}", strWhere);
            return int.Parse(ExecuteScalar(ConfigHelper.ConnectionStrings, strSql.ToString()).ToString());
        }

        public G_PurchaseOrderPriceInfo GetG_PurchaseOrderPriceInfoModel(int purchaseOrderPriceId)
        {
            G_PurchaseOrderPriceInfo model = new G_PurchaseOrderPriceInfo();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append("PurchaseOrderPriceId");
            strSql.Append(",PurchaseOrderId");
            strSql.Append(",Price");
            strSql.Append(",Remart");
            strSql.Append(",Status");
            strSql.Append(",AddUser");
            strSql.Append(",AddTime");
            strSql.Append(",LastEditUser");
            strSql.Append(",LastEditTime");
            strSql.Append(" FROM G_PurchaseOrderPriceInfo WITH(NOLOCK) WHERE PurchaseOrderPriceId=@PurchaseOrderPriceId");
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@PurchaseOrderPriceId", SqlDbType.Int, 4) { Value = purchaseOrderPriceId });
            using (SqlDataReader r = this.ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()))
            {
                int PurchaseOrderPriceId = r.GetOrdinal("PurchaseOrderPriceId");
                int PurchaseOrderId = r.GetOrdinal("PurchaseOrderId");
                int Price = r.GetOrdinal("Price");
                int Remart = r.GetOrdinal("Remart");
                int Status = r.GetOrdinal("Status");
                int AddUser = r.GetOrdinal("AddUser");
                int AddTime = r.GetOrdinal("AddTime");
                int LastEditUser = r.GetOrdinal("LastEditUser");
                int LastEditTime = r.GetOrdinal("LastEditTime");
                if (r.Read())
                {
                    model.PurchaseOrderPriceId = r.GetInt32(PurchaseOrderPriceId);
                    model.PurchaseOrderId = r.GetInt32(PurchaseOrderId);
                    model.Price = r.GetDecimal(Price);
                    model.Remart = r.GetString(Remart);
                    model.Status = r.GetInt32(Status);
                    model.AddUser = r.GetInt32(AddUser);
                    model.AddTime = r.GetDateTime(AddTime);
                    model.LastEditUser = r.GetInt32(LastEditUser);
                    model.LastEditTime = r.GetDateTime(LastEditTime);
                }
                r.Close();
            }
            return model;
        }
    }
}
